/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionnote.modelservice;

import fr.p10.miage.gestionabsence.model.Connection;
import fr.p10.miage.gestionnote.model.Formation;
import fr.p10.miage.gestionnote.model.Semestre;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author gensky
 */
public class FormationServices extends UtilService{

    private static Connection cx;

    public static Formation getFormationById() {
        return null;
    }

    public static List<Formation> getFormations(int idEtudiant) throws SQLException {
        List<Formation> listeFormations = new ArrayList<Formation>();
        String sql = "SELECT f.idf,p.idp, f.grade, f.niveau, f.filiere, f.parcours, f.typee, p.annee\n" +
                    "FROM etudiant_promotion ep, formation f, etudiant e, promotion p\n" +
                    "WHERE ep.ide = e.id\n" +
                    "AND ep.idp = p.idp\n" +
                    "AND p.idf = f.idf\n" +
                    "AND e.id = "+idEtudiant+"\n"+
                    "ORDER BY p.annee DESC";
        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                listeFormations.add(new Formation(
                        resultat.getInt("idf"),
                        resultat.getInt("idp"),
                        idEtudiant,
                        resultat.getString("grade"),
                        resultat.getString("niveau"),
                        resultat.getString("filiere"),
                        resultat.getString("parcours"),
                        resultat.getString("typee"),
                        resultat.getInt("annee")));
            }
            resultat.close();
        }

        pst.close();
        return listeFormations;
    }

    public static List<Semestre> getSemestres(int idFormation, int idPromotion,int idEtudiant) throws SQLException {
        List<Semestre> listeSemestres = new ArrayList<Semestre>();

        String sql = "SELECT *\n"
                + "FROM formation f, semestre s\n"
                + "WHERE f.idf = s.idf\n"
                + "AND f.idf = " + idFormation + ""
                + "ORDER BY s.idsem";
        PreparedStatement pst = cx.getBDD().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, 
   ResultSet.CONCUR_READ_ONLY);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            
            resultat.last();
            int rowCount = resultat.getRow();
            resultat.beforeFirst();

            if (rowCount > 2) {
//                System.out.println(rowCount + " superieur à 2 semestre");
                int idS1[] = new int[20];
                int idS2[] = new int[20];
                int nbperiodS1 = 0;
                int nbperiodS2 = 0; 
                int iS1 = 0;
                int iS2 = 0;
                int nperiode = 0;
                while (resultat.next()) {
                    
                    nperiode = resultat.getInt("nperiode");
                    int id = resultat.getInt("idsem");
                    
                    if(nperiode == 1 || nperiode ==2)//on est dans le S1
                    {
                        idS1[iS1]=id;
                        nbperiodS1=nperiode;
                        iS1++;
                    }
                    if(nperiode == 3 || nperiode ==4 || nperiode ==5) // on est dans le s2
                    {
                        idS2[iS2]=id;
                        nbperiodS2=nperiode;
                        iS2++;
                    }
                    
                }

                listeSemestres.add(new Semestre(idS1,idPromotion,idFormation, nperiode,1,idEtudiant)); //Ajout S1
                listeSemestres.add(new Semestre(idS2,idPromotion,idFormation,nperiode,2,idEtudiant)); //Ajout S2

            } else {
                int i = 0;
                while (resultat.next()) {
                    listeSemestres.add(new Semestre(resultat.getInt("idsem"),idPromotion,idFormation,resultat.getInt("nperiode"),i+1,idEtudiant ));
                    i++;
                }
            }

            resultat.close();
        }

        pst.close();
        return listeSemestres;
    }

    public static int getAnnee(int idf) throws SQLException, Exception {
        int annee = 0;
        String sql = "SELECT p.annee"
                + "FROM formation f, promotion p"
                + "WHERE ef.idf = " + idf + ""
                + "AND p.idf(+) = f.idf";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("annee") != 0) {
                    annee = resultat.getInt("annee");
                }
            } else {
                throw new Exception("Login incorrect");
            }
            resultat.close();
        }
        pst.close();
        return annee;

    }
}
